shp2pgsql st_transform not unique requires casting

[robe]

shp2pgsql is emitting code like this

ST_Transform('0101000020E61000009A081B9E5E9557C0006F8104C51F4440', 900913)

If you use ST_Transform. However if you have raster installed in your db, you get an ST_Transform is not unique notice, because raster also has an ST_Transform function.

This needs to be changed to

ST_Transform('0101000020E61000009A081B9E5E9557C0006F8104C51F4440'::geometry, 900913);

See user posting: ​http://postgis.refractions.net/pipermail/postgis-users/2012-April/033534.html

[mcayland]

Fix ST_Transform for shp2pgsql

[mcayland]

How about the attached patch which adds explicit casts for both geometry/geography and passes regression for me?

[robe]

Works for me. Fill free to commit.

[robe]

Mark, Hope you don't mind I've committed at r9621 . I think this is an important fix and I would like to get out a 2.0.1 experimental windows build out sometime soon.

[robe]

Just a note — I forgot to check the geography type to see if this works. We don't have an ST_Transform for geography so it may not work. I'll reopen if it doesn't work for geography.

[mcayland]

Okay - I was away at my parents over Easter and their WiFi doesn't always work that well, so struggling to get internet access. The cast should be fine - for the first version of the patch, I added the cast inside ST_Transform() which tripped up one of the regression tests (thanks to strk for adding this for the new feature!) which is why the explicit cast now wraps everything.

[robe]

Evidentally I missed testing this on geography:

I tested with this command:

%SHP2PGSQL% -c -s 4269:4326 -G -g geog  -W "latin1" tl_2010_42_zcta510 tiger_staging.zcta510_42 > zip.sql

And it needs to cast the geometry first before transforming so I guess you need to make the same change for geography.

Note:

It produces

ST_Transform('gubblygook',4326)::geography

and needs to produce:

ST_Transform('gubblygook'::geometry,4326)::geography

[robe]

Fixed for trunk at r9965 and 2.0 at r9964

Mark et. al feel free to make further changes if you are not satisfied with my fix.

[mcayland]

Looks fine to me. I was just about to post that I couldn't reproduce this, however I see that I didn't have rtpostgis.sql loaded into my test database which is why I didn't pick up on this before.